﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Grishko.SerilizeTools;
using System.Xml;

namespace Grishko.DataTier.SqlScriptor
{
    /// <summary>
    /// скрипт машина - списки объктов.
    /// 
    /// </summary>
    public  class SqlScriptEngine
    {
        #region Var
        /// <summary>
        /// Пространство приска - набор пар параметр значение соотв запросу
        /// </summary>
        public const string ScriptConst_SerachAreaTable                 = "#SerachAreaTable";           // пространство поиска
        public const string ScriptConst_SrchRoot                        = "#SrchRoot";                  //корни дерева свойств
        public const string ScriptConst_TreePropertyCollection          = "#TreePropertyCollection";    //коллекция древовидных свойств запроса
        public const string ScriptConst_PopulatedObjectTree             = "#PopulatedObjectTree";      //заполненое дерево свойств
        public const string ScriptConst_SelectedObjectId                = "#SelectedObjectId";      //отобранные Id 
        public const string ScriptConst_Page_SelectedObjectId           = "@Page_SelectedObjectId ";      //страница отобранные Id 
        
        

        private SelectCommand selectCommand = null;
        #endregion

#region
    private  string EntityTypeTagName
        {
            get
            {
                //Это поле пишется в базу поиска
                object[] arr = selectCommand.SearchAreaTemplateType.GetCustomAttributes(typeof(TagNameInfo), true);
                if (arr.Length == 0)
                    throw new Exception(string.Format("ObjectWhereSentence.TagName нет TagNameInfo у типа {0}",
                                                       new string[] { selectCommand.SearchAreaTemplateType.ToString() }));

                return ((TagNameInfo)arr[0]).TagName;
            }

        }
#endregion

        #region Public
        /// <summary>
        /// Конструктор
        /// </summary>
        /// <param name="cmd"></param>
        public  SqlScriptEngine(SelectCommand cmd)
        {
            selectCommand = cmd;
        }

        /// <summary>
        /// Основной метод - полуить скрипт. 
        /// </summary>
        /// <returns></returns>
        public string GetScript()
        {            
            StringBuilder writer = new StringBuilder();
            Script_DeclareVars_0(writer);                                    //Сформировать переменые   
            Script_BuildSearchArea_1 (writer);                              //Сформировать область поиска
            
            Script_BuildPropertyTree_20(writer);                             //построить вершину дерева
            Script_BuildPropertyTree_2(writer);                             //построить граф объекта свойств
            
            Script_CalculateFunctionProperty_3(writer);                     //Расчитать - свойства функции
            Script_CalculateTreeProperty_4(writer);                         //Расчитать древовидные свойства
            Script_CompareValueProperty_5(writer);                          //Сравнить значения получить выборку
            Script_SetDataPage_6(writer);                                   //Разбить результат на страницы - взять нужную
            Script_GetObjects_7(writer);                                    //Полуить объекты

            return writer.ToString();
        }

        protected virtual void Script_DeclareVars_0(StringBuilder writer)
        {            
            writer.Append("CREATE TABLE " + ScriptConst_SelectedObjectId + "(ObjectId varchar(36))" + Environment.NewLine);
            writer.Append("CREATE TABLE "+ScriptConst_TreePropertyCollection  +"  (Objectid varchar(36),TeeValueId varchar(36)) " + Environment.NewLine);
            writer.Append("CREATE TABLE " + ScriptConst_PopulatedObjectTree + " (ObjectID varchar(36), PropertyName varchar(max) ,TxtProperty varchar(max), PropertyValue varchar(max),TxtPropertyValue varchar(max),ValueIsSqlFunction bit)" + Environment.NewLine);
            

        }

        /// <summary>
        /// Сформировать область поиска
        /// </summary>
        /// <param name="writer"></param>
        protected virtual void Script_BuildSearchArea_1(StringBuilder writer)
        {
            ScriprSearchAreaEngine eng = new ScriprSearchAreaEngine(selectCommand);
            writer.Append(eng.GetScript());
            writer.Append("DECLARE @Page_SelectedObjectId TABLE (ObjectId varchar(36))");
            writer.Append("DECLARE @PropertyArray TABLE (ObjectId varchar(36), 	Property varchar(max), ItemCount int)" + Environment.NewLine);
            writer.Append("DECLARE @SelectedObjectId_ TABLE (ObjectId varchar(36))" + Environment.NewLine);

        }

        protected virtual void Script_BuildPropertyTree_20(StringBuilder writer)
        {
            string sAddWhere = String.Empty;
            string[] ScriptParams = { ScriptConst_SrchRoot, ScriptConst_SerachAreaTable, EntityTypeTagName };
            string FormatString =
                                 "select distinct  " + Environment.NewLine +
                                 " ObjectID " + Environment.NewLine +
                                 " into " + Environment.NewLine +
                                 " {0} " + Environment.NewLine +
                                 " from " + Environment.NewLine +
                                 " {1} as Source " + Environment.NewLine +
                                 " where " + Environment.NewLine +
                                 " Source.TagName='{2}' " + Environment.NewLine;                                 
            writer.Append(string.Format(FormatString, ScriptParams));
        }

        /// <summary>
        /// построить граф объекта свойств
        /// </summary>
        /// <param name="writer"></param>
        protected virtual void Script_BuildPropertyTree_2(StringBuilder writer)
        {
            ScriprPropertyTreeEngine eng = new ScriprPropertyTreeEngine(selectCommand);
            writer.Append(eng.GetScript());
        }

        /// <summary>
        /// Расчитать - свойства функции
        /// </summary>
        /// <param name="writer"></param>
        protected virtual void Script_CalculateFunctionProperty_3(StringBuilder writer)
        {
            string FormatString = "-- посчитать значения функций " + Environment.NewLine +
              "-- Расчет функций " + Environment.NewLine +
              "DECLARE @ObjectID varchar(36)" + Environment.NewLine +
              "DECLARE @PropretyName Varchar(255)" + Environment.NewLine +
              "DECLARE @SQL nvarchar(500);" + Environment.NewLine +
              "DECLARE @SQL_ID nvarchar(500);" + Environment.NewLine +
              "DECLARE @ParmDefinition nvarchar(500);" + Environment.NewLine +
              "DECLARE @Result Varchar(max)" + Environment.NewLine +
              "DECLARE @CalculateResults TABLE (SqlFuncion varchar(max), SqlFuncionValue varchar(max))" + Environment.NewLine +
              "DECLARE function_cursor CURSOR FOR select ObjectID, PropertyName, PropertyValue  from "+ScriptConst_PopulatedObjectTree +" where ValueIsSqlFunction=1" + Environment.NewLine +
              "OPEN function_cursor " + Environment.NewLine +
              "FETCH NEXT FROM function_cursor " + Environment.NewLine +
              "INTO @ObjectID, @PropretyName,@SQL " + Environment.NewLine +
              "WHILE @@FETCH_STATUS = 0 " + Environment.NewLine +
              "BEGIN " + Environment.NewLine +
              "   SET @ParmDefinition = N'@ResultOUT varchar(max) OUTPUT'; " + Environment.NewLine +
              "   SET @SQL_ID=@SQL  " + Environment.NewLine +
              "   SET @SQL =N'SET @ResultOUT = '+ @SQL " + Environment.NewLine +
              "   EXECUTE sp_executesql @SQL,@ParmDefinition, @ResultOUT=@Result OUTPUT; " + Environment.NewLine +
              "   INSERT INTO @CalculateResults VALUES(@SQL_ID,@Result) " + Environment.NewLine +
              "FETCH NEXT FROM function_cursor " + Environment.NewLine +
              "INTO @ObjectID, @PropretyName,@SQL " + Environment.NewLine +
              "END " + Environment.NewLine +
              "CLOSE function_cursor " + Environment.NewLine +
              "DEALLOCATE function_cursor" + Environment.NewLine +
              "UPDATE  POT " + Environment.NewLine +
              "SET " + Environment.NewLine +
              "    POT.PropertyValue=CR.SqlFuncionValue " + Environment.NewLine +
              "    ,POT.TxtPropertyValue=CR.SqlFuncionValue " + Environment.NewLine +
              "FROM " + Environment.NewLine +
              "    " + ScriptConst_PopulatedObjectTree + " POT JOIN @CalculateResults  CR " + Environment.NewLine +
              "ON " + Environment.NewLine +
              "   POT.PropertyValue=  CR.SqlFuncion " + Environment.NewLine;
            writer.Append(FormatString);
        }

        
        /// <summary>
        /// Расчитать древовидные свойства
        /// </summary>
        /// <param name="writer"></param>
        protected virtual void Script_CalculateTreeProperty_4(StringBuilder writer)
        {
        }
        
        /// <summary>
        /// Сравнить значения получить выборку
        /// </summary>
        /// <param name="writer"></param>
        protected virtual void Script_CompareValueProperty_5(StringBuilder writer)
        {
            ScriptCompareValuePropertyEngine eng = new ScriptCompareValuePropertyEngine(selectCommand);
            writer.Append ( eng.GetScript());
        }

        /// <summary>
        /// Разбить результат на страницы - взять нужную
        /// </summary>
        /// <param name="writer"></param>
        protected virtual void Script_SetDataPage_6(StringBuilder writer)
        {
            string[] scriptParams = { selectCommand.StartRecord.ToString(), selectCommand.EndRecord.ToString(), ScriptConst_SelectedObjectId , ScriptConst_Page_SelectedObjectId };
            string FormatString =                                 
                                 "INSERT INTO {3} SELECT ObjectId	FROM  " + Environment.NewLine +
                                 "(SELECT  " + Environment.NewLine +
                                 "ObjectId, " + Environment.NewLine +
                                 "ROW_NUMBER()  OVER (ORDER BY ObjectId) AS row_num " + Environment.NewLine +
                                 "FROM " + Environment.NewLine +
                                 "{2}) " + Environment.NewLine +
                                 "AS Page 			" + Environment.NewLine +
                                 "WHERE row_num BETWEEN  {0} and {1}" + Environment.NewLine;
            writer.Append(string.Format(FormatString, scriptParams));
        }

        /// <summary>
        /// Полуить объекты
        /// </summary>
        /// <param name="writer"></param>
        protected virtual void Script_GetObjects_7(StringBuilder writer)
        {
            string FormatString =
                                "DECLARE @OutputRwCnt AS INT " + Environment.NewLine +
                                "SELECT @OutputRwCnt=COUNT(*) FROM @Page_SelectedObjectId      " + Environment.NewLine +
                                "DECLARE @res AS XML " + Environment.NewLine +
                                "SELECT @res=(SELECT vSrchStorageLinks.ObjectXML.query('.')  FROM @Page_SelectedObjectId T join vSrchStorageLinks on vSrchStorageLinks.ID=T.objectId FOR XML PATH(''), TYPE, ELEMENTS , ROOT('EntityList'))" + Environment.NewLine +
                                "IF not(@res is null)   SET @res.modify('insert attribute OutputRwCnt {sql:variable(''@OutputRwCnt'')} into (/EntityList)[1]')" + Environment.NewLine +
                                "select @res " + Environment.NewLine;
            writer.Append(FormatString);
        }
        #endregion
     
        public ICollection<EntitySingle> GetCollection()
        {
            string sql = string.Empty;
            SecuretyContext sc = AppContext.GetSC();
            EntityList result = new EntityList();
            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
            try
            {   
                sql = this.GetScript();
                
                if (string.IsNullOrEmpty(sql))
                                    return result.ToList <EntitySingle>();
                    

                
                cmd.CommandText = sql;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Connection = sc.Connection;
                sc.Connection.Open();
                XmlReader r = cmd.ExecuteXmlReader();
                if (null == r)
                        return result.ToList<EntitySingle>();

                XmlDocument d = new XmlDocument();
                d.Load(r);
                if (null == d.FirstChild)
                    return result.ToList<EntitySingle>();                
                
                result.ReadXml (d.FirstChild);
                return result.ToList<EntitySingle>();

            }

            finally
            {
                cmd.Connection.Close();
                sc.Connection.Close();
            }
        }

        
    }
}
